package lookups.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import lookups.entity.Address;
import lookups.entity.Country;
import lookups.entity.County;
import lookups.entity.Town;

public class AddressDAO {
	
	private String DATABASE_NAME="MockProject";
	private String TABLE_NAME="Address";
	private String USERNAME="sa";
	private String PASSWORD="12345678";
	
	private ArrayList<Address> listAddress;

	public ArrayList<Address> getListAddress() {
		return listAddress;
	}

	public void setListAddress(ArrayList<Address> listAddress) {
		this.listAddress = listAddress;
	}
	
	public ArrayList<Address> getListAddress(String postCode, String street, String town,
															int pageNum,int pageSize){
		Connection con = null;
		int rowStart=(pageNum-1)*pageSize+1;
		int rowEnd=rowStart+pageSize-1;
		
		String sql="SELECT TB.countryId,TB.countryName,TB.countyId,TB.countyName"
				 + 		  ",TB.townId,TB.townName,TB.addressId,TB.addressName,TB.postCode "
				 +" FROM ("
				 +		"SELECT cr.countryId,cr.countryName,c.countyId,c.countyName"
				 +				",t.townId,t.townName,a.addressId,a.addressName,a.postCode"
				 +				", ROW_NUMBER() OVER (ORDER BY a.addressId) AS RowNum "
				 +		"FROM "+TABLE_NAME+" a " 
				 +			"JOIN Countrytb cr ON cr.countryId=a.countryId "
				 +			"JOIN Countytb c ON c.countyId=a.countyId "
				 +			"JOIN Town t ON t.townId=a.townId "
				 +		"WHERE ";
						sql+=" a.postCode LIKE '%"+postCode+"%' ";
						sql+=" AND a.addressName LIKE '%"+street+"%' ";
						sql+=" AND t.townName LIKE '%"+town+"%'";
					
					sql+=") AS TB "
				+" WHERE TB.RowNum BETWEEN "+rowStart+" AND "+rowEnd+" ";
        
        System.out.println("sql= "+sql);
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
        	String connectionUrl ="jdbc:sqlserver://localhost:1130;" + 
        							"database="+DATABASE_NAME+";" + 
        							"user="+USERNAME+";" + 
        							"password="+PASSWORD+";"; 
        	con = DriverManager.getConnection(connectionUrl); 
        	Statement stmt = con.createStatement();  
        	ResultSet rs=stmt.executeQuery(sql);
        	
        	listAddress=new ArrayList<Address>();
        	
        	while(rs.next()){
        		
        		String countryId=rs.getString(1);
        		String countryName=rs.getString(2);
        		String countyId=rs.getString(3);
        		String countyName=rs.getString(4);
        		int townId=rs.getInt(5);
        		String townName=rs.getString(6);
        		int addressId=rs.getInt(7);
        		String addressName=rs.getString(8);
        		String postCodes =rs.getString(9);
        		
        		Country cr=new Country(countryId,countryName);
        		County c=new County(countyId,cr,countyName);
        		Town t=new Town(townId,c, cr, townName);
        		Address a=new Address(addressId, t, c, cr, addressName, postCodes);
        		
        		listAddress.add(a);
        		System.out.println(addressName);
        	}
        	return listAddress;
        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
        
		return null;
	}
	
	public int getTotalAddress(String postCode, String street, String town){
		int count=0;
		Connection con = null;
		
		String sql="SELECT cr.countryId,cr.countryName,c.countyId,c.countyName,t.townId,t.townName,a.addressId,a.addressName,a.postCode "+
					"FROM "+TABLE_NAME+" a " +
						"JOIN Countrytb cr ON cr.countryId=a.countryId "+
						"JOIN Countytb c ON c.countyId=a.countyId "+
						"JOIN Town t ON t.townId=a.townId "+
					"WHERE ";
		
        	sql+="a.postCode LIKE '%"+postCode+"%' ";
        		sql+=" AND a.addressName LIKE '%"+street+"%' ";
        		sql+=" AND t.townName LIKE '%"+town+"%'";
        
        System.out.println("sqltotal= "+sql);
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
        	String connectionUrl ="jdbc:sqlserver://localhost:1130;" + 
        							"database="+DATABASE_NAME+";" + 
        							"user="+USERNAME+";" + 
        							"password="+PASSWORD+";"; 
        	con = DriverManager.getConnection(connectionUrl); 
        	Statement stmt = con.createStatement();  
        	ResultSet rs=stmt.executeQuery(sql);
        	
        	listAddress=new ArrayList<Address>();
        	
        	while(rs.next()){
        		count++;
        	}
        	return count;
        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
        
		return 0;
	}
}
